﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

using MCEMediaManager.Model;
using TvdbLib;
using TvdbLib.Data;

namespace MCEMediaManager.Data
{
    public class MediaDAL
    {
        public static SqlConnection GetConnection()
        {
            SqlConnection conn = new SqlConnection(Application.Current.DataStore.GetString("DBConnectionString", ""));
            conn.Open();
            return conn;
        }

        public static Episode GetEpisode(string seriesName, int seasonNum, int episodeNum)
        {
            Episode tempEpisode = null;

            SqlCommand command = new SqlCommand("sp_GetEpisodeBySeriesSeasonEpisode", MediaDAL.GetConnection());
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("SeriesName", seriesName));
            command.Parameters.Add(new SqlParameter("SeasonNum", seasonNum));
            command.Parameters.Add(new SqlParameter("EpisodeNum", episodeNum));

            IDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
            if (dr.Read())
            {
                tempEpisode = new Episode();
                tempEpisode.LoadFromDataReader(dr);
            }
            dr.Close();
            if (tempEpisode == null)
            {
                Series tempSeries = MediaDAL.GetSeries(seriesName);
                if (tempSeries != null)
                {
                    TvdbEpisode tvdbEpisode = Application.Current.TVDBData.Handler.GetEpisode(tempSeries.TvdbID, seasonNum, episodeNum, TvdbEpisode.EpisodeOrdering.DefaultOrder, TvdbLanguage.DefaultLanguage);
                    tempEpisode = new Episode();
                    tempEpisode.LoadFromTvdbEpisode(tvdbEpisode);
                    MediaDAL.SaveEpisode(tempEpisode);
                }
            }
            return tempEpisode;
        }

        public static Series GetSeries(string seriesName)
        {
            Series tempSeries = null;
            SqlCommand command = new SqlCommand("sp_GetSeriesByName", MediaDAL.GetConnection());
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("SeriesName", seriesName));
            IDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
            if (dr.Read())
            {
                tempSeries = new Series();
                tempSeries.LoadFromReader(dr);
            }
            dr.Close();
            if (tempSeries == null)
            {
                List<TvdbSearchResult> results = Application.Current.TVDBData.Handler.SearchSeries(seriesName);
                if (results.Count > 0)
                {
                    tempSeries = new Series();
                    tempSeries.LoadFromTvdbSearchResults(results[0]);
                    MediaDAL.SaveSeries(tempSeries);
                }
            }
            return tempSeries;
        }

        public static void SaveSeries(Series theSeries)
        {
            SqlCommand command = new SqlCommand("sp_UpdateSeries");
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("ID", theSeries.ID));
            command.Parameters.Add(new SqlParameter("TvdbID", theSeries.TvdbID));
            command.Parameters.Add(new SqlParameter("Name", theSeries.Name));
            command.Parameters.Add(new SqlParameter("Overview", theSeries.Overview));
            command.Parameters.Add(new SqlParameter("ContentRating", theSeries.ContentRating));
            command.Parameters.Add(new SqlParameter("Rating", theSeries.Rating));
            command.Parameters.Add(new SqlParameter("OriginalAirDate", theSeries.OriginalAirDate));
            command.Parameters.Add(new SqlParameter("Status", theSeries.Status));
            command.Parameters.Add(new SqlParameter("Banner", theSeries.Banner));
            command.Parameters.Add(new SqlParameter("FanArt", theSeries.FanArt));
            command.Parameters.Add(new SqlParameter("Runtime", theSeries.Runtime));
            command.Connection = MediaDAL.GetConnection();
            command.ExecuteNonQuery();
            command.Connection.Close();
        }
        public static void SaveEpisode(Episode theEpisode)
        {
            SqlCommand command = new SqlCommand("sp_UpdateEpisode");
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("ID", theEpisode.ID));
            command.Parameters.Add(new SqlParameter("TvdbID", theEpisode.TvdbID));
            command.Parameters.Add(new SqlParameter("SeriesID", theEpisode.SeriesID));
            command.Parameters.Add(new SqlParameter("SeriesTvdbID", theEpisode.SeriesTvdbID));
            command.Parameters.Add(new SqlParameter("Name", theEpisode.Name));
            command.Parameters.Add(new SqlParameter("EpisodeNumber", theEpisode.EpisodeNumber));
            command.Parameters.Add(new SqlParameter("SeasonNumber", theEpisode.SeasonNumber));
            command.Parameters.Add(new SqlParameter("OriginalAirDate", theEpisode.OriginalAirDate));
            command.Parameters.Add(new SqlParameter("Overview", theEpisode.Overview));
            command.Parameters.Add(new SqlParameter("Rating", theEpisode.Rating));
            command.Parameters.Add(new SqlParameter("SeasonTvdbID", theEpisode.SeasonTvdbID));
            command.Connection = MediaDAL.GetConnection();
            command.ExecuteNonQuery();
            command.Connection.Close();
        }
    }
}
